<?php
class Seminar_Controller extends AdminController {
    
    public function __construct() {
        parent::__construct('seminar');
        $this->setDefaultListOrder('begin_date');
    }
    
    function all() {
        $this->initPagination('seminar');
        
        $this->viewData['seminarList'] = $this->dao->queryAll($this->orderLimitAndOffset(
                'SELECT sem.*, l10n.name ' .
                'FROM seminar sem ' .
                'LEFT OUTER JOIN seminar_l10n l10n ON sem.id = l10n.seminar_id AND l10n.language_code = :language_code'),
                array('language_code' => $this->getL10nLanguageCode()));
        
        $this->renderSubview('admin/seminar/seminarList');
    }
    
    function display() {
        $id = $this->input->get('id');

        // Basic validation
        if (! valid::isId($id)) {
            $this->redirectToList('error.invalidId');
        }

        $lang = $this->getL10nLanguageCode();

        // Fill the models:
        $this->viewData['seminar'] = $this->dao->fetchSeminar($id, $lang);

        if (! $this->viewData['seminar']) {
            $this->redirectToList('error.recordNotFound');
        }

        $this->viewData['batchList'] = $this->dao->queryAll(
                'SELECT b.*, l10n.name, l10n.description ' .
                'FROM batch b ' .
                'LEFT OUTER JOIN batch_l10n l10n ON l10n.batch_code = b.batch_code AND l10n.seminar_id = :seminar_id AND l10n.language_code = :language_code ' .
                'WHERE b.seminar_id = :seminar_id',
                array('seminar_id' => $id, 'language_code' => $lang)
        );

        $this->viewData['usedBatchTypeList'] = $this->dao->queryAll(
                'SELECT t.*, l10n.* ' .
                'FROM batch_type t ' .
                'JOIN batch_type_l10n l10n ON t.code = l10n.batch_type_code AND l10n.language_code = :lang ' .
                'WHERE code IN (SELECT batch_type_code FROM batch WHERE seminar_id = :seminar_id)',
                array('seminar_id' => $id, 'lang' => $lang)
        );

        // Must contain countries array and currency record
        $this->viewData['priceGroupList'] = $this->dao->queryAll(
                'SELECT * FROM price_group WHERE seminar_id = :seminar_id ORDER BY id',
                array('seminar_id' => $id)
        );
        foreach ($this->viewData['priceGroupList'] as $i => $priceGroup) {
            $this->viewData['priceGroupList'][$i]['countries'] = $this->dao->queryAll(
                    'SELECT c.* FROM country c ' .
                    'WHERE c.code IN (SELECT country_code FROM price_group_country WHERE price_group_id = :price_group_id)',
                    array('price_group_id' => $priceGroup['id'])
            );
            $this->viewData['priceGroupList'][$i]['currency'] = $this->dao->queryOne(
                    'SELECT * FROM currency WHERE code = :currency_code',
                    array('currency_code' => $priceGroup['currency_code'])
            );
        }


        $this->viewData['programTypeList'] = $this->dao->queryAll(
                'SELECT DISTINCT t.*, l10n.* ' .
                'FROM available_program_type bpt ' .
        		'JOIN program_type t ON t.code = bpt.program_type_code ' .
        		'JOIN batch b ON b.batch_code = bpt.batch_code AND b.seminar_id = bpt.seminar_id ' .
                'LEFT OUTER JOIN program_type_l10n l10n ON l10n.program_type_code = bpt.program_type_code AND l10n.language_code = :lang ' .
                'WHERE b.seminar_id = :seminar_id',
                array('seminar_id' => $id, 'lang' => $lang)
        );

        // array[$programTypeCode] = array[batchCode]
        $this->viewData['programTypeBatchMap'] = array();
        foreach ($this->viewData['programTypeList'] as $programType) {
            $this->viewData['programTypeBatchMap'][$programType['code']] = $this->dao->queryFirstValues(
                    'SELECT b.batch_code FROM available_program_type sbpt ' .
                    'JOIN batch b ON b.batch_code = sbpt.batch_code AND b.seminar_id = sbpt.seminar_id ' .
                    'WHERE b.seminar_id = :seminar_id AND sbpt.program_type_code = :program_type_code',
                    array('seminar_id' => $id, 'program_type_code' => $programType['code'])
            );
        }
        
        $this->viewData['karmayogaTypeList'] = $this->dao->queryAll(
                'SELECT skt.*, l10n.* ' .
                'FROM available_karmayoga_type skt ' .
                'JOIN karmayoga_type_l10n l10n ON l10n.karmayoga_type_code = skt.karmayoga_type_code AND l10n.language_code = :lang ' .
                'WHERE skt.seminar_id = :seminar_id',
                array('seminar_id' => $id, 'lang' => $lang)
        );

        // Accomodation types available during the seminar
        $this->viewData['accomodationTypeList'] = $this->dao->queryAll(
                'SELECT DISTINCT sbat.accomodation_type_code AS code, l10n.* ' .
                'FROM available_accommodation_type sbat ' .
                'JOIN batch b ON b.batch_code = sbat.batch_code AND b.seminar_id = sbat.seminar_id ' .
                'JOIN accomodation_type_l10n l10n ON l10n.accomodation_type_code = sbat.accomodation_type_code AND l10n.language_code = :lang ' .
                'WHERE b.seminar_id = :seminar_id',
                array('seminar_id' => $id, 'lang' => $lang)
        );

        // array[$accomodationTypeCode] = array[batchCode]
        $this->viewData['accomodationTypeBatchMap'] = array();
        foreach ($this->viewData['accomodationTypeList'] as $accomodationType) {
            $this->viewData['accomodationTypeBatchMap'][$accomodationType['code']] = $this->dao->queryFirstValues(
                    'SELECT b.batch_code FROM available_accommodation_type sbat ' .
                    'JOIN batch b ON b.batch_code = sbat.batch_code AND b.seminar_id = sbat.seminar_id ' .
                    'WHERE b.seminar_id = :seminar_id AND sbat.accomodation_type_code = :accomodation_type_code',
                    array('seminar_id' => $id, 'accomodation_type_code' => $accomodationType['code'])
            );
        }

        // TODO move to prices page
        // array[batchType][$accomodationTypeCode][priceGroupId] = price
        /* $this->viewData['accomodationTypePriceGroupMap'] = array();
        foreach ($this->viewData['usedBatchTypeList'] as $batchType) {
            $oneBatchTypeMap = array();
            foreach ($this->viewData['accomodationTypeList'] as $accomodationType) {
                $oneBatchTypeMap[$accomodationType['code']] = array();
                foreach ($this->viewData['priceGroupList'] as $priceGroup) {
                    $oneBatchTypeMap[$accomodationType['code']][$priceGroup['id']] = $this->dao->queryOneValue(
                            'SELECT price FROM accomodation_type_price WHERE price_group_id = :price_group_id AND accomodation_type_code = :accomodation_type_code AND batch_type_code = :batch_type_code',
                            array(
                                'price_group_id' => $priceGroup['id'],
                                'accomodation_type_code' => $accomodationType['code'],
                                'batch_type_code' => $batchType['code']
                            )
                    );
                }
            }
            $this->viewData['accomodationTypePriceGroupMap'][$batchType['code']] = $oneBatchTypeMap;
        } */

        $this->viewData['exerciseGroupList'] = $this->dao->queryAll(
                'SELECT seg.exercise_group_code AS code, l10n.name ' .
                'FROM available_exercise_group seg ' .
                'JOIN exercise_group_l10n l10n ON l10n.exercise_group_code = seg.exercise_group_code AND l10n.language_code = :lang ' .
                'WHERE seminar_id = :seminar_id',
                array('seminar_id' => $id, 'lang' => $lang)
        );

        // Program types for single day batches except satsang and co.
        $this->viewData['singleDayProgramListWithoutShort'] = $this->dao->queryAll(
        		'SELECT DISTINCT pt.code, l10n.name, l10n.description ' .
        		'FROM available_program_type sbpt ' .
        		'JOIN batch b ON b.batch_code = sbpt.batch_code AND b.seminar_id = sbpt.seminar_id AND b.batch_type_code = :singleDayBatchTypeCode ' .
        		'JOIN program_type pt ON pt.code = sbpt.program_type_code AND pt.single_days_only != :true ' .
        		'LEFT OUTER JOIN program_type_l10n l10n ON l10n.program_type_code = pt.code AND l10n.language_code = :lang ' .
        		'WHERE b.seminar_id = :seminarId ' .
        		'ORDER BY name',
        		array(
        				'seminarId' => $id,
        				'singleDayBatchTypeCode' => constants::$singleDayBatchTypeCode,
        				'true' => sql::$TRUE,
        				'lang' => $this->getLocalLanguageCode()
        		)
        );

        $this->viewData['socialDiscount'] = $this->dao->getSeminarSocialDiscount($id);
        $this->viewData['earlyDiscount'] = $this->dao->getSeminarEarlyDiscount($id);
        $this->viewData['cancellationCharge'] = $this->dao->getSeminarCancellationCharge($id);

        $this->renderSubview('admin/seminar/seminarDisplay');
    }
    
    function edit() {
    }
    
    protected function loadFormData() {
    }
    
    function save() {
        $this->redirect('/admin/seminar/all');
    }
    
    function delete() {
        $id = $this->input->get('id');
        
        // Basic validation
        if (! valid::isId($id)) {
            $this->redirectToList('error.invalidId');
        }

        $lang = $this->getL10nLanguageCode();
        $seminar = $this->dao->fetchSeminar($id, $lang);

        $this->dao->getDb()->beginTransaction();
        try {
            $param = array('seminar_id' => $id);

            // Delete extra charges
            $this->dao->executeQuery('DELETE FROM seminar_early_discount WHERE seminar_id = :seminar_id', $param);
            $this->dao->executeQuery('DELETE FROM seminar_cancellation_charge WHERE seminar_id = :seminar_id', $param);

            // Delete discounts
            $this->dao->executeQuery('DELETE FROM seminar_social_discount WHERE seminar_id = :seminar_id', $param);

            // Delete short term stay prices
            $this->dao->executeQuery('DELETE FROM short_program WHERE seminar_id = :seminar_id', $param);

            // Delete list of available exercise groups
            $this->dao->executeQuery('DELETE FROM available_exercise_group WHERE seminar_id = :seminar_id', $param);

            // Delete accomodation types
            $this->dao->executeQuery('DELETE FROM available_accommodation_type WHERE seminar_id = :seminar_id AND batch_code IN (SELECT batch_code FROM batch WHERE seminar_id = :seminar_id)', $param);

            // Delete the karmayoga types
            $this->dao->executeQuery('DELETE FROM available_karmayoga_type WHERE seminar_id = :seminar_id', $param);
            
            // Delete program types available in batches
            $this->dao->executeQuery('DELETE FROM available_program_type WHERE seminar_id = :seminar_id AND batch_code IN (SELECT batch_code FROM batch WHERE seminar_id = :seminar_id)', $param);

            // Delete price groups
            $this->dao->executeQuery('DELETE FROM price_group_country WHERE price_group_id IN (SELECT id FROM price_group WHERE seminar_id = :seminar_id)', $param);
            $this->dao->executeQuery('DELETE FROM price_group WHERE seminar_id = :seminar_id', $param);

            // Delete batches
            $this->dao->executeQuery('DELETE FROM batch_l10n WHERE seminar_id = :seminar_id AND batch_code IN (SELECT batch_code FROM batch WHERE seminar_id = :seminar_id)', $param);
            $this->dao->executeQuery('DELETE FROM batch WHERE seminar_id = :seminar_id', $param);

            // Delete the seminar
            $this->dao->executeQuery('DELETE FROM seminar_l10n WHERE seminar_id = :seminar_id', $param);
            $this->dao->delete('seminar', $id);

            $this->dao->getDb()->commit();
        }
        catch (Exception $e) {
            // If something raised an exception in our transaction block of statements,
            // roll back any work performed in the transaction
            $this->dao->getDb()->rollBack();

            throw $e;
        }
        
        $this->msg->info('seminar.deleteSuccess', $seminar['name']);
        $this->redirectToList();
    }
    
    function activate() {
        $id = $this->input->get('id');
        
        // Basic validation
        if (! valid::isId($id)) {
            $this->redirectToList('error.invalidId');
        }

        $lang = $this->getL10nLanguageCode();
        $seminar = $this->dao->fetchSeminar($id, $lang);
        if (! $seminar) {
            $this->redirectToList('error.recordNotFound');
        }
        
        $seminarToEdit = $this->dao->getById('seminar', $id);
        $seminarToEdit['active'] = sql::$TRUE;
        
        $this->dao->update('seminar', $seminarToEdit);
        $this->msg->info('seminar.activateSucces', $seminar['name']);
        $this->redirect('/admin/seminar/display?id=' . $id);
    }
    
    public function exportForForeignPolice() {
    	$id = $this->input->get('id');
    	
    	// Basic validation
    	if (! valid::isId($id)) {
    		$this->redirectToList('error.invalidId');
    	}
    	
    	$seminar = $this->dao->fetchSeminar($id, $this->getL10nLanguageCode());
    	if (! $seminar) {
    		$this->redirectToList('error.recordNotFound');
    	}
    	
    	$recordList = array();
    	// $recordList[] = array('begin_date', 'end_date', 'first_name', 'last_name', 'birth_date', 'citizenship', 'idcard_number', 'idcard_type', 'street', 'city', 'postal_code');
    	
    	$result = $this->dao->queryAll(
    			'SELECT b.begin_date, b.end_date, r.person_id, p.first_name, p.last_name, p.birth_date, p.idcard_number, p.idcard_type, p.street, p.city, p.postal_code, cry.name AS citizenship ' .
    			'FROM registration r ' .
    			'JOIN person p ON r.person_id = p.id ' .
    			'JOIN country cry ON p.citizenship_code = cry.code ' .
    			'JOIN batch b ON r.batch_code = b.batch_code AND r.seminar_id = b.seminar_id ' .
    			'JOIN category c ON c.category_code = r.category_code AND c.seminar_id = r.seminar_id ' .
    			'JOIN program_type pt ON pt.code = c.program_type_code ' .
    			'WHERE r.is_present = ' . sql::$TRUE . ' ' .
    			'AND pt.with_accommodation = ' . sql::$TRUE . ' ' .
    			'ORDER BY p.last_name, p.first_name, p.birth_date, r.person_id, b.begin_date');
    	for ($i = 0; $i < count($result); $i++) {
    		$row = $result[$i];
    		$person = $row;
    		
    		// Merge subsequent registrations of the same person
    		while ($i + 1 < count($result) &&
    				$result[$i]['person_id'] == $result[$i + 1]['person_id'] &&
    				$result[$i]['end_date'] == $result[$i + 1]['begin_date']) {
    			$i++;
    		}
    		$person['end_date'] = $result[$i]['end_date'];
    		
    		$recordList[] = $person;
    	}
    	
    	// $tmpFileName = excel::simpleReport($recordList);
    	// download::force($tmpFileName, NULL, 'foreign_police.xlsx');
    	return $this->createPdfForPolice($recordList);
    }
    
    private function createPdfForPolice($personList) {
    	require_once Kohana::find_file('vendor/tcpdf', 'tcpdf');
    	
    	// create new PDF document
    	$pdf = new TCPDF('L', PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
    
    	// set document information
    	$pdf->SetCreator(PDF_CREATOR);
    	$pdf->SetAuthor('Nicola Asuni');
    	$pdf->SetTitle('TCPDF Example 006');
    	$pdf->SetSubject('TCPDF Tutorial');
    	$pdf->SetKeywords('TCPDF, PDF, example, test, guide');
    
    	// set default header data
    	// $pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, PDF_HEADER_TITLE.' 006', PDF_HEADER_STRING);
    
    	// set header and footer fonts
    	// $pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
    	// $pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
    
    	// remove default header/footer
    	$pdf->setPrintHeader(false);
    	$pdf->setPrintFooter(false);
    
    	// set default monospaced font
    	$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
    
    	// set margins
    	$pdf->SetMargins(5, 5, 15);
    	// $pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
    	// $pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
    
    	// set auto page breaks
    	$pdf->SetAutoPageBreak(TRUE, 5);
    
    	// set some language-dependent strings (optional)
    	if (file_exists(dirname(__FILE__).'/lang/eng.php')) {
    		require_once(dirname(__FILE__).'/lang/eng.php');
    		$pdf->setLanguageArray($l);
    	}
    
    	$pdf->SetFont('freeserif', '', 10);
    
    	// ---------------------------------------------------------
    	// Print a table
    
    	// $pdf->setCellPaddings(5,10,5,10);
    
    	$tdAndTr = '<tr><td style="border-bottom: 0.3px solid silver">';
    
    	// create some HTML content
    	$personIndex = 0;
    	$html = '';
    	foreach ($personList as $person) {
    		if ($personIndex % 8 == 0) {
    			$pdf->AddPage();
    			$html .= '<table cellpadding="4" cellspacing="0">';
    		}
    		if ($personIndex % 4 == 0) {
    			$html .= '<tr>';
    		}
    			
    		$cellBorder = 'border: 0.3px solid black';
    		if ($personIndex % 4 > 3) {
    			$cellBorder = 'border-right: 0.3px solid black; border-bottom: 0.3px solid black';
    		}
    			
    		$html .= '<td style="height: 10cm; ' . $cellBorder . '">' .
    				'<table cellpadding="4" cellspacing="1" border="0">' .
    				$tdAndTr .
    				'Ubytován<br>od: ' . format::date($person['begin_date']) . ' do: ' . format::date($person['end_date']) . '<br>' .
    				'Příjmení: ' . $person['last_name'] . '</td></tr>' .
    				$tdAndTr .
    				'Jméno: ' . $person['first_name'] . '</td></tr>' .
    				$tdAndTr .
    				'Datum narození: ' . format::date($person['birth_date']) . '<br>' .
    				'Stát. občanství: ' . $person['citizenship'] . '</td></tr>' .
    				$tdAndTr .
    				'Účel pobytu: jógový seminář</td></tr>' .
    				$tdAndTr .
    				'Příjezd dne: ' . format::date($person['begin_date']) . '<br>' .
    				'Číslo pasu: ' . $person['idcard_number'] . '</td></tr>' .
    				$tdAndTr .
    				'Trvalé bydliště:<br>' . $person['street'] . '<br>' . $person['postal_code'] . ', ' . $person['city'] . '</td></tr>' .
    				'<tr><td>' .
    				'Ubytovatel:<br></td></tr>' .
    				'</table>' .
    				'</td>';
    			
    		if ($personIndex % 4 == 3 || $personIndex + 1 == count($personList)) {
    			$tmpIndex = $personIndex;
    			while ($tmpIndex % 4 < 3) {
    				$html .= '<td></td>';
    				$tmpIndex++;
    			}
    			$html .= '</tr>';
    		}
    		if ($personIndex % 8 == 7 || $personIndex + 1 == count($personList)) {
    			$tmpIndex = $personIndex;
    			$html .= '</table>';
    			$pdf->writeHTML($html, true, false, true, false, '');
    			$html = '';
    		}
    		$personIndex ++;
    	}
    
    	// output the HTML content
    	// $pdf->writeHTML($html, true, false, true, false, '');
    
    	// reset pointer to the last page
    	$pdf->lastPage();
    
    	// ---------------------------------------------------------
    
    	//Close and output PDF document
    	$pdf->Output('foreign_police.pdf', 'I');
    }
    
    public function karmayogaAssignment() {
    	$id = $this->input->get('id');
    	
    	// Basic validation
    	if (! valid::isId($id)) {
    		$this->redirectToList('error.invalidId');
    	}
    	
    	$seminar = $this->dao->fetchSeminar($id, $this->getL10nLanguageCode());
    	if (! $seminar) {
    		$this->redirectToList('error.recordNotFound');
    	}
    	
    	$this->msg->info('Tato funkčnost ještě není implementována.');
    	$this->redirect('/admin/seminar/display?id=' . $id);
    }
}
